﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows;
using System.Windows.Controls;
using wms.Common;

namespace wms.Pages
{
    /// <summary>
    /// GoodsImport.xaml 的交互逻辑
    /// </summary>
    public partial class GoodsImport : Window
    {
        private int _result = 0;
        private DataTable _dt = null;

        public GoodsImport(DataTable dt = null)
        {
            ChangeLanguage.changePage(Variable.LanguageSeesion.ToString());
            InitializeComponent();
            _dt = dt;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            this.btn_delete.IsEnabled = this.dataGrid.SelectedIndex == -1 ? false : true;
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] { new DataColumn("name"), new DataColumn("code"), new DataColumn("weight"), new DataColumn("type"), new DataColumn("size"), new DataColumn("value"), new DataColumn("client") });
            this.dataGrid.ItemsSource = dt.DefaultView;

            if (this._dt != null)
            {
                foreach (DataRow dr in this._dt.Rows)
                {
                    DataRow dr_new = dt.NewRow();
                    dr_new["name"] = dr["name"];
                    dr_new["code"] = dr["code"];
                    dr_new["weight"] = dr["weight"];
                    dr_new["type"] = dr["type"];
                    dr_new["size"] = dr["size"];
                    dr_new["value"] = dr["value"];
                    dr_new["client"] = dr["client"];
                    dt.Rows.Add(dr_new);
                }
            }
        }

        private void btn_import_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.OpenFileDialog dialog = new Microsoft.Win32.OpenFileDialog();
            dialog.Filter = "Microsoft Excel 97-2003/2007文件|*.xls;*.xlsx";
            if (dialog.ShowDialog() == true)
            {
                Dictionary<string, string> mapping = new Dictionary<string, string>();
                mapping.Add("物品名称", "name");
                mapping.Add("物品编码", "code");
                mapping.Add("重量(kg)", "weight");
                mapping.Add("物品尺寸", "size");
                mapping.Add("声明价值", "value");
                mapping.Add("物品类型", "type");
                mapping.Add("客户", "client");
                this.dataGrid.ItemsSource = ExcelHelper.ExcelToTable(dialog.FileName, mapping).DefaultView;
            }
        }

        private void btn_delete_Click(object sender, RoutedEventArgs e)
        {
            var items = this.dataGrid.SelectedItems;
            List<DataRowView> list = new List<DataRowView>();
            foreach (var item in items)
            {
                list.Add(item as DataRowView);
            }

            foreach (var item in list)
            {
                if (item != null)
                {
                    item.Delete();
                }
            }
        }

        private void dataGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            this.btn_delete.IsEnabled = this.dataGrid.SelectedIndex == -1 ? false : true;
        }


        private void btn_add_Click(object sender, RoutedEventArgs e)
        {
            DataTable dt = (this.dataGrid.ItemsSource as DataView).Table;
            if (dt.Rows.Count==0)
            {
                MessageBox.Show("数据不能为空");
                return;
            }
            string sql = "insert into t_goods(code,name,weight,type,size,value,client) values ";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            int rows = 0; //记录总行数

            //验证并且构造sql语句和参数
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                try
                {
                    //1、忽略空行
                    bool blank = true;
                    foreach (DataColumn column in dt.Columns)
                    {
                        if (!string.IsNullOrWhiteSpace(Convert.ToString(dr[column.ColumnName])))
                        {
                            blank = false;
                            break;
                        }
                    }
                    if (blank == true)
                    {
                        continue;
                    }

                    //2、合规性验证
                    string name = Utils.Parse(dr["name"]);
                    string code = Utils.Parse(dr["code"]);
                    float? weight = Utils.ParseFloat(dr["weight"]);
                    string type = Utils.Parse(dr["type"]);
                    string size = Utils.Parse(dr["size"]);
                    float? value = Utils.ParseFloat(dr["value"]);
                    string client = Utils.Parse(dr["client"]);

                    if (value == null || value < 0)
                    {
                        MessageBox.Show("第" + (i + 1) + "行，声明价值无效");
                        return;
                    }
                    if (weight != null && weight < 0)
                    {
                        MessageBox.Show("第" + (i + 1) + "行，重量不能小于0");
                        return;
                    }
                    if (string.IsNullOrWhiteSpace(code))
                    {
                        MessageBox.Show("第" + (i + 1) + "行，物品编码不能为空");
                        return;
                    }
                    int count = DBHelper.GetCount("select code from t_goods where code = @code", new SqlParameter[] { new SqlParameter("@code", code) });
                    if (count > 0)
                    {
                        MessageBox.Show("第" + (i + 1) + "行，物品编码已经存在");
                        return;
                    }

                    if (string.IsNullOrWhiteSpace(type))
                    {
                        type = this.txt_type.Text;
                    }

                    //3、拼接sql和参数
                    sql = sql + string.Format("(@code{0},@name{0},@weight{0},@type{0},@size{0},@value{0},@client{0}),", i);

                    parameters.Add("@code" + i, code);
                    parameters.Add("@name" + i, name);
                    parameters.Add("@weight" + i, weight);
                    parameters.Add("@type" + i, type);
                    parameters.Add("@size" + i, size);
                    parameters.Add("@value" + i, value);
                    parameters.Add("@client" + i, client);
                    rows++;
                }
                catch (Exception e1)
                {
                    MessageBox.Show("执行失败" + e1.Message);
                    return;
                }
            }

            if (rows == 0)
            {
                MessageBox.Show("没有数据");
                return;
            }

            if (sql.EndsWith(","))
            {
                sql = sql.Remove(sql.Length - 1);
            }


            try
            {
                this.Result = DBHelper.ExcuteSQL(sql, parameters);
                MessageBox.Show("执行完毕，本次保存" + this.Result + "条");
                this.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show("执行失败\n" + ex.Message);
            }
        }

        public int Result
        {
            get
            {
                return _result;
            }

            set
            {
                _result = value;
            }
        }

        private void btn_select_type_Click(object sender, RoutedEventArgs e)
        {
            Type win = new Type();
            bool? dialogResult = win.ShowDialog();
            if (dialogResult != null && dialogResult == true)
            {
                this.txt_type.Text = win.Value.Id + ")" + win.Value.Name;
            }
        }
    }
}
